import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class jdbc_secret_equivalence_query_0002 {
    //创建数据库连接。
    public static Connection GetConnection(String username, String passwd) {
    String driver = "org.postgresql.Driver";
    String sourceURL =  "jdbc:postgresql://host_ip:db_port/database_name?url_params";
    Connection conn = null;
    try {
        //创建数据库连接。
        conn = DriverManager.getConnection(sourceURL, username, passwd);
        System.out.println("Connection succeed!");
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    return conn;
    }
    //执行SQL语句，创建加密表
    public static void CreateTable(Connection conn) {
        try {
            Statement stmt = conn.createStatement();
            //创建CMK
            int rc1 = stmt.executeUpdate("drop client master key if exists imgcmk1;");
            int rc2 = stmt.executeUpdate("create client master key imgcmk1 with ( key_store = localkms, key_path = 'key_path_value' , algorithm = rsa_3072);");
            //创建CEM
            int rc3 = stmt.executeUpdate("drop column encryption key if exists imgcek1;");
            int rc4 = stmt.executeUpdate("create column encryption key imgcek1 with values (client_master_key = imgcmk1, algorithm  = aead_aes_256_cbc_hmac_sha256);");
            //创建加密表
            int rc5 = stmt.executeUpdate("drop table if exists t_qeuivalence_case002;");
            int rc6 = stmt.executeUpdate("create table t_qeuivalence_case002 (id_number int, name varchar(50) encrypted with (column_encryption_key = imgcek1, encryption_type = deterministic),credit_card  varchar(19) encrypted with (column_encryption_key = imgcek1, encryption_type = deterministic));");
            stmt.close();
            //执行加密表的批处理操作
            PreparedStatement pstmt = conn.prepareStatement("insert into t_qeuivalence_case002 (id_number, name, credit_card) values (?,?,?)");
            //针对每条数据都要调用setShort设置参数，以及调用addBatch确认该条设置完毕
            int loopCount = 20;
            for (int i = 1; i < loopCount + 1; ++i) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "Name " + i);
                pstmt.setString(3, "Address " + i);
                pstmt.addBatch();
            }
            // 调用PreparedStatement的executeBatch方法执行批处理。
            int[] rowcount = pstmt.executeBatch();
            pstmt.close();
            System.out.println("executeBatch_result = " + rowcount);
            System.out.println("批处理操作执行成功！");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("executeBatch_result = " + e.getMessage());
            System.out.println("批处理操作执行失败！");
        }
        try {
           //清理环境
           Statement stmt_drop = conn.createStatement();
           int clear_rc1 = stmt_drop.executeUpdate("drop table t_qeuivalence_case002 cascade;");
           int clear_rc2 = stmt_drop.executeUpdate("drop column encryption key imgcek1;");
           int clear_rc3 = stmt_drop.executeUpdate("drop client master key if exists imgcmk1;");
           stmt_drop.close();
           System.out.println("drop_result = " + clear_rc3);
           System.out.println("Execution Succeeded!");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("drop_result = " + e.getMessage());
            System.out.println("Execution Faild!");
        }
    }

    public static void main(String[] args) throws InterruptedException {
        //创建数据库连接。
        Connection conn = GetConnection("conn_user", "conn_passwd");
        //创建表。
        CreateTable(conn);
        //关闭数据库连接。
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
   }
}